﻿Public Class Gestion_Cliente
    Enum estado
        inicial
        insertar
        modificar
    End Enum
    Dim operacion As estado = estado.inicial
    Dim nombre_tabla As String = "cliente"
    Dim tratamiento As New Tratamiento_Objetos
    Dim datos_clase As New datos

    Dim documentos As New AutoCompleteStringCollection
    Dim barrios As New AutoCompleteStringCollection
    Dim sexos As New AutoCompleteStringCollection

    Private Sub soloLetras(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_nombre.KeyPress, txt_apellido.KeyPress
        If Not Char.IsLetter(e.KeyChar) And Not Char.IsWhiteSpace(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.KeyChar = ""
        End If
    End Sub
    Private Sub soloNumeros(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_telefono.KeyPress, txt_numero.KeyPress, txt_numDoc.KeyPress
        If Not Char.IsNumber(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.KeyChar = ""
        End If
    End Sub

    Private Sub botones(ByVal flag As Boolean)
        Me.btn_nuevo.Enabled = flag
        Me.btn_salir.Enabled = flag
        Me.btn_cancelar.Enabled = Not flag
        Me.btn_guardar.Enabled = Not flag
        Me.btn_eliminar.Enabled = Not flag
        Me.btn_modificar.Enabled = Not flag
    End Sub

    Private Function validar() As Boolean
        Dim flag As Boolean = True
        If String.IsNullOrEmpty(Me.txt_numero.Text) Then
            lbl_numero.ForeColor = Color.Red
            txt_numero.Focus()
            flag = False
        Else
            lbl_numero.ForeColor = Color.Black
        End If
        If String.IsNullOrEmpty(Me.txt_calle.Text) Then
            lbl_calle.ForeColor = Color.Red
            txt_calle.Focus()
            flag = False
        Else
            lbl_calle.ForeColor = Color.Black
        End If
        If cmb_barrio.SelectedIndex = -1 Then
            lbl_barrio.ForeColor = Color.Red
            cmb_barrio.Focus()
            flag = False
        Else
            lbl_barrio.ForeColor = Color.Black
        End If
        If cmb_localidad.SelectedIndex = -1 Then
            lbl_localidad.ForeColor = Color.Red
            cmb_localidad.Focus()
            flag = False
        Else
            lbl_localidad.ForeColor = Color.Black
        End If
        If String.IsNullOrEmpty(Me.txt_telefono.Text) Then
            lbl_telefono.ForeColor = Color.Red
            txt_telefono.Focus()
            flag = False
        Else
            lbl_telefono.ForeColor = Color.Black
        End If
        If String.IsNullOrEmpty(Me.txt_email.Text) Then
            lbl_email.ForeColor = Color.Red
            txt_email.Focus()
            flag = False
        Else
            lbl_email.ForeColor = Color.Black
        End If

        If cmb_sexo.SelectedIndex = -1 Then
            lbl_sexo.ForeColor = Color.Red
            cmb_sexo.Focus()
            flag = False
        Else
            lbl_sexo.ForeColor = Color.Black
        End If

        If Not IsDate(msk_fechaNacimiento.Text) Then
            lbl_fechaNacimiento.ForeColor = Color.Red
            msk_fechaNacimiento.Focus()
            flag = False
        Else
            Dim fechaActual As New Date
            fechaActual = Date.Now
            Dim fecha As Date
            fecha = Date.Parse(msk_fechaNacimiento.Text)
            If fecha > fechaActual Then
                MessageBox.Show("Fecha no valida", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                lbl_fechaNacimiento.ForeColor = Color.Red
                msk_fechaNacimiento.Focus()
                flag = False
            Else
                lbl_fechaNacimiento.ForeColor = Color.Black
            End If
        End If
        If String.IsNullOrEmpty(Me.txt_nombre.Text) Then
            lbl_nombre.ForeColor = Color.Red
            txt_nombre.Focus()
            flag = False
        Else
            lbl_nombre.ForeColor = Color.Black
        End If
        If String.IsNullOrEmpty(Me.txt_apellido.Text) Then
            lbl_apellido.ForeColor = Color.Red
            txt_apellido.Focus()
            flag = False
        Else
            lbl_apellido.ForeColor = Color.Black
        End If
        If String.IsNullOrEmpty(Me.txt_numDoc.Text) Then
            lbl_numDoc.ForeColor = Color.Red
            txt_numDoc.Focus()
            flag = False
        Else
            lbl_numDoc.ForeColor = Color.Black
        End If
        If cmb_tipoDoc.SelectedIndex = -1 Then
            lbl_tipoDoc.ForeColor = Color.Red
            cmb_tipoDoc.Focus()
            flag = False
        Else
            lbl_tipoDoc.ForeColor = Color.Black
        End If
        Return flag
    End Function
    Private Function validarUso() As Boolean
        Dim condicion As String = "id_tipo_documento = " & cmb_tipoDoc.SelectedValue _
                                  & " and numero_documento = " & txt_numDoc.Text
        Dim datos As Data.DataTable = datos_clase.leo_tabla("factura", condicion)
        If datos.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Sub Gestion_Cliente_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        botones(True)
        cargarTipoDocumento()
        cargarLocalidades()
        cargarClientes()
        cargarSexos()
        tratamiento.habilitar(False, Me.Controls)
        btn_nuevo.Focus()
        tratamiento.limpiar(Me.Controls)

        cmb_localidad.DropDownStyle = ComboBoxStyle.DropDownList

        cmb_tipoDoc.AutoCompleteMode = AutoCompleteMode.Suggest
        cmb_tipoDoc.AutoCompleteSource = AutoCompleteSource.CustomSource
        cmb_tipoDoc.AutoCompleteCustomSource = documentos

        cmb_barrio.AutoCompleteMode = AutoCompleteMode.Suggest
        cmb_barrio.AutoCompleteSource = AutoCompleteSource.CustomSource
        cmb_barrio.AutoCompleteCustomSource = barrios

        cmb_sexo.AutoCompleteMode = AutoCompleteMode.Suggest
        cmb_sexo.AutoCompleteSource = AutoCompleteSource.CustomSource
        cmb_sexo.AutoCompleteCustomSource = sexos
    End Sub

    Private Sub cargarTipoDocumento()
        Dim tabla As Data.DataTable = datos_clase.leo_tabla("tipo_documento")
        cmb_tipoDoc.Items.Clear()
        cmb_tipoDoc.DataSource = tabla
        cmb_tipoDoc.ValueMember = "id_tipo_documento"
        cmb_tipoDoc.DisplayMember = "nombre"
        Dim fila As Integer = 0
        For fila = 0 To tabla.Rows.Count - 1
            documentos.Add(tabla.Rows(fila)(1))
        Next
    End Sub

    Private Sub cargarLocalidades()
        Dim tabla As Data.DataTable = datos_clase.leo_tabla("localidad")
        cmb_localidad.Items.Clear()
        cmb_localidad.DataSource = tabla
        cmb_localidad.ValueMember = "id_localidad"
        cmb_localidad.DisplayMember = "nombre"
    End Sub

    Private Sub cargarBarrios()

        Dim condicion As String = "id_localidad = " & cmb_localidad.SelectedValue.ToString
        Dim tabla As Data.DataTable = datos_clase.leo_tabla("barrio", condicion)
        cmb_barrio.DataSource = tabla
        cmb_barrio.ValueMember = "id_barrio"
        cmb_barrio.DisplayMember = "nombre"
        barrios.Clear()
        Dim fila As Integer = 0
        For fila = 0 To tabla.Rows.Count - 1
            barrios.Add(tabla.Rows(fila)(1))
        Next
        cmb_barrio.SelectedIndex = -1
    End Sub


    Private Sub cargarSexos()
        Dim tabla As Data.DataTable = datos_clase.leo_tabla("sexo")
        cmb_sexo.Items.Clear()
        cmb_sexo.DataSource = tabla
        cmb_sexo.ValueMember = "id_sexo"
        cmb_sexo.DisplayMember = "nombre"
        Dim fila As Integer = 0
        For fila = 0 To tabla.Rows.Count - 1
            sexos.Add(tabla.Rows(fila)(1))
        Next
    End Sub
    Private Sub cargarClientes()
        Dim conexion As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim data As New Data.DataTable
        conexion.ConnectionString = datos_clase._cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        Dim sql As String = "SELECT tipo_documento.nombre , cliente.numero_documento, cliente.nombre, cliente.apellido FROM (cliente INNER JOIN tipo_documento on cliente.id_tipo_documento = tipo_documento.id_tipo_documento)"
        cmd.CommandText = sql
        data.Load(cmd.ExecuteReader())
        conexion.Close()
        Dim fila As Integer = 0

        DataGridView1.Rows.Clear()

        For fila = 0 To data.Rows.Count - 1
            DataGridView1.Rows.Add()
            DataGridView1.Rows(fila).Cells("tipo_doc").Value = data.Rows(fila)(0)
            DataGridView1.Rows(fila).Cells("numero_documento").Value = data.Rows(fila)(1)
            DataGridView1.Rows(fila).Cells("nombre").Value = data.Rows(fila)(2)
            DataGridView1.Rows(fila).Cells("apellido").Value = data.Rows(fila)(3)
        Next
    End Sub

   
    Private Sub btn_nuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nuevo.Click
        operacion = estado.insertar
        botones(False)
        Me.btn_eliminar.Enabled = False
        Me.btn_modificar.Enabled = False
        tratamiento.habilitar(True, Me.Controls)
        Me.cmb_tipoDoc.Focus()
    End Sub

    Private Sub btn_guardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_guardar.Click
        If validar() = True Then
            If validarExistencia = False Then
                If MessageBox.Show("¿Desea guardar el " & nombre_tabla & "?", "Confirmacion", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                    If guardar() = 1 Then
                        MessageBox.Show("Carga realizada con exito.", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        cargarClientes()
                        operacion = estado.inicial
                    Else
                        MessageBox.Show("Error al realizar la carga.", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                    tratamiento.limpiar(Me.Controls)
                    tratamiento.habilitar(False, Me.Controls)
                    botones(True)
                    btn_nuevo.Focus()
                End If
            Else
                MessageBox.Show("El cliente ya esta cargado :).", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                cmb_tipoDoc.Focus()
            End If

        End If
    End Sub
 
    Private Sub btn_modificar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_modificar.Click
        If validar() = True Then
            If MessageBox.Show("¿Desea modificar el " & nombre_tabla & "?", "Consulta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                Dim conexion As New SqlClient.SqlConnection
                Dim cmd As New SqlClient.SqlCommand
                Dim consulta As String = ""
                conexion.ConnectionString = datos_clase._cadena
                conexion.Open()
                consulta = "update " & nombre_tabla
                consulta &= " set nombre = '" & Me.txt_nombre.Text & "',"
                consulta &= " apellido = '" & Me.txt_apellido.Text & "',"
                consulta &= " calle = '" & Me.txt_calle.Text & "',"
                consulta &= " numero = '" & Me.txt_numero.Text & "',"
                consulta &= " email = '" & Me.txt_email.Text & "',"
                consulta &= " telefono = '" & Me.txt_telefono.Text & "',"
                Dim fecha As Date
                fecha = Date.Parse(msk_fechaNacimiento.Text)
                consulta &= " fecha_nacimiento = '" & fecha & "',"
                consulta &= " id_sexo = '" & Me.cmb_sexo.SelectedValue & "',"
                consulta &= " id_barrio = '" & Me.cmb_barrio.SelectedValue & "'"
                consulta &= " where numero_documento = '" & Me.txt_numDoc.Text _
                    & "' and id_tipo_documento = '" & cmb_tipoDoc.SelectedValue & "'"
                cmd.CommandType = CommandType.Text
                cmd.CommandText = consulta
                cmd.Connection = conexion
                Dim filas As Integer = cmd.ExecuteNonQuery()
                conexion.Close()

                If filas = 1 Then
                    MessageBox.Show("Modificacion realizada.", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    operacion = estado.inicial
                Else
                    MessageBox.Show("Error al modificar", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    operacion = estado.inicial
                End If
                cargarClientes()
                tratamiento.limpiar(Me.Controls)
                tratamiento.habilitar(False, Me.Controls)
                operacion = estado.inicial
                botones(True)
            End If
        End If
    End Sub
    Private Sub btn_eliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_eliminar.Click
        If MessageBox.Show("¿Desea eliminar el " & nombre_tabla & "?", "Pregunta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            If validarUso() = False Then
                Dim conexion As New SqlClient.SqlConnection
                Dim cmd As New SqlClient.SqlCommand
                Dim consulta As String = "delete from " & nombre_tabla & " where numero_documento = '" & Me.txt_numDoc.Text _
                                         & "' and id_tipo_documento = '" & cmb_tipoDoc.SelectedValue & "'"
                conexion.ConnectionString = datos_clase._cadena
                conexion.Open()
                cmd.Connection = conexion
                cmd.CommandType = CommandType.Text
                cmd.CommandText = consulta
                Dim filas As Integer = cmd.ExecuteNonQuery()
                conexion.Close()
                If filas = 1 Then
                    MessageBox.Show("Eliminacion realizada.", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    cargarClientes()
                    operacion = estado.inicial
                Else
                    MessageBox.Show("Error al eliminar", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    operacion = estado.inicial
                End If
                tratamiento.limpiar(Me.Controls)
                tratamiento.habilitar(False, Me.Controls)
                botones(True)
            Else
                MessageBox.Show("Existen facturas usando este " & nombre_tabla & ". Elimine las facturas.", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End If
    End Sub
    Private Sub btn_salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_salir.Click
        Close()
    End Sub

    Private Sub DataGridView1_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        Me.operacion = estado.modificar
        Dim temp As String = "nombre = '" & DataGridView1.CurrentRow.Cells(0).Value & "'"
        Dim tabla_doc As Data.DataTable = datos_clase.leo_tabla("tipo_documento", temp)
        Dim condicion As String = "numero_documento = '" & DataGridView1.CurrentRow.Cells(1).Value _
                                         & "' and id_tipo_documento = '" & tabla_doc.Rows(0)(0) & "'"
        Dim tabla As Data.DataTable = datos_clase.leo_tabla("cliente", condicion)

        Me.txt_numDoc.Text = tabla.Rows(0)(0)
        Me.cmb_tipoDoc.SelectedValue = tabla.Rows(0)(1)
        Me.txt_nombre.Text = tabla.Rows(0)(2)
        Me.txt_apellido.Text = tabla.Rows(0)(3)
        Me.msk_fechaNacimiento.Text = tabla.Rows(0)(4)
        Me.cmb_sexo.SelectedValue = tabla.Rows(0)(5)
        Me.txt_calle.Text = tabla.Rows(0)(6)
        Me.txt_numero.Text = tabla.Rows(0)(7)
        Me.txt_email.Text = tabla.Rows(0)(9)
        Me.txt_telefono.Text = tabla.Rows(0)(10)

        Dim id_barrio As String = tabla.Rows(0)(8)
        Dim condicion2 As String = "id_barrio = " & id_barrio
        Dim tabla_barrio As Data.DataTable = datos_clase.leo_tabla("barrio", condicion2)
        Me.cmb_localidad.SelectedValue = tabla_barrio.Rows(0)(2)
        cargarBarrios()
        Me.cmb_barrio.SelectedValue = tabla_barrio.Rows(0)(0)

        tratamiento.habilitar(True, Me.Controls)
        tratamiento.labels_Color(Color.Black, Me.Controls)
        txt_numDoc.Enabled = False
        cmb_tipoDoc.Enabled = False
        botones(False)
        Me.btn_guardar.Enabled = False
    End Sub

    Private Sub Gestion_Cliente_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        If operacion = estado.inicial Then
            If MessageBox.Show("¿Desea salir?", "Alerta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
                e.Cancel = True
            End If
        Else
            MessageBox.Show("Termine la operacion actual!", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            e.Cancel = True
        End If
    End Sub

    Private Sub cmb_localidad_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_localidad.SelectionChangeCommitted
        cargarBarrios()
    End Sub

    Private Function guardar() As Integer
        Dim fecha As Date
        fecha = Date.Parse(msk_fechaNacimiento.Text)

        Dim conexion As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        conexion.ConnectionString = datos_clase._cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        Dim sql As String = "insert into " & nombre_tabla & "(numero_documento,id_tipo_documento,nombre,apellido,fecha_nacimiento,id_sexo,calle,numero,id_barrio,email,telefono) values ('"
        sql &= txt_numDoc.Text & "','" & cmb_tipoDoc.SelectedValue & "','" & txt_nombre.Text & "','" & txt_apellido.Text & "','" & fecha & "','"
        sql &= cmb_sexo.SelectedValue & "','" & txt_calle.Text & "','" & txt_numero.Text & "','" & cmb_barrio.SelectedValue & "','" & txt_email.Text & "','" & txt_telefono.Text & "')"
        cmd.CommandText = sql
        Dim temporal As Integer = cmd.ExecuteNonQuery
        conexion.Close()
        Return temporal
    End Function


    Private Sub btn_cancelar_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_cancelar.Click
        If operacion = estado.insertar Then
            If MessageBox.Show("¿Desea cancelar la carga?", "Confirmacion", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                tratamiento.limpiar(Me.Controls)
                tratamiento.habilitar(False, Me.Controls)
                tratamiento.labels_Color(Color.Black, Me.Controls)
                Me.btn_nuevo.Focus()
                botones(True)
                operacion = estado.inicial
            End If
        Else
            If MessageBox.Show("¿Desea cancelar la modificacion?", "Confirmacion", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                tratamiento.limpiar(Me.Controls)
                tratamiento.habilitar(False, Me.Controls)
                tratamiento.labels_Color(Color.Black, Me.Controls)
                Me.btn_nuevo.Focus()
                botones(True)
                operacion = estado.inicial
            End If
        End If
    End Sub

    Private Function validarExistencia() As Boolean
        Dim condicion As String = "id_tipo_documento = " & cmb_tipoDoc.SelectedValue _
                                     & " and numero_documento = " & txt_numDoc.Text
        Dim datos As Data.DataTable = datos_clase.leo_tabla("cliente", condicion)
        If datos.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
End Class